﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace GeneralDataAccess {
    public abstract class DataAccessBase {
        protected string ConnectionString { get; private set; }
        protected SqlConnection Connection { get; private set; }
        private SqlCommand Command { get; set; }

        public DataAccessBase(string pConnectionString) {
            if (!string.IsNullOrEmpty(pConnectionString)) {
                ConnectionString = pConnectionString;
                Connection = new SqlConnection(ConnectionString);
                Command = null;
            }
            else {
                throw new Exception("No se encontro la cadena de conexion");
            }
        }

        protected void IniciarConsulta() {
            Command = Connection.CreateCommand();
        }

        protected void AdicionarParametro(string pParametro, object pValor) {
            if (pParametro[0] == '@')
                Command.Parameters.AddWithValue(pParametro, pValor);
            else
                Command.Parameters.AddWithValue("@" + pParametro, pValor);
        }

        public SqlDataReader ExecuteReader(string pSqlQuery, CommandType pCommandType) {
            // HACK: Crear un flag o solucionar con un enum, para verificar que realmente
            //       se haya iniciado una nueva consulta.
            //if (Command != null)
            //    throw new Exception("Se debe inciar una nueva consulta");

            Command.CommandText = pSqlQuery;
            Command.CommandType = pCommandType;

            try {
                Connection.Open();
                return Command.ExecuteReader();
            }
            catch (Exception ex) {
                throw ex;
            }
            finally {
                Connection.Close();
                Command = null;
            }
        }
        public void ExecuteNonQuery(string pSqlQuery, CommandType pCommandType) {
            //if (Command != null)
            //    throw new Exception("Se debe inciar una nueva consulta");

            Command.CommandText = pSqlQuery;
            Command.CommandType = pCommandType;

            try {
                Connection.Open();
                Command.ExecuteNonQuery();
            }
            catch (Exception ex) {
                throw ex;
            }
            finally {
                Connection.Close();
                Command = null;
            }
        }
        public T ExecuteScalar<T>(string pSqlQuery, CommandType pCommandType) {
            //if (Command != null)
            //    throw new Exception("Se debe inciar una nueva consulta");

            Command.CommandText = pSqlQuery;
            Command.CommandType = pCommandType;

            try {
                Connection.Open();
                return (T)Command.ExecuteScalar();
            }
            catch (Exception ex) {
                throw ex;
            }
            finally {
                Connection.Close();
                Command = null;
            }
        }
        public DataTable GetDataTable(string pSqlQuery, CommandType pCommandType) {
            //if (Command != null)
            //    throw new Exception("Se debe inciar una nueva consulta");

            Command.CommandText = pSqlQuery;
            Command.CommandType = pCommandType;

            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = Command;

            DataTable tblResultado = new DataTable();

            try {
                adapter.Fill(tblResultado);

                return tblResultado;
            }
            catch (Exception ex) {
                throw ex;
            }
        }
        public List<T> GetObjectCollection<T>(string pSqlQuery, CommandType pCommandType) {
            //if (Command != null)
            //    throw new Exception("Se debe inciar una nueva consulta");

            Command.CommandText = pSqlQuery;
            Command.CommandType = pCommandType;

            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = Command;

            DataTable tblResultado = GetDataTable(pSqlQuery, pCommandType);

            if (tblResultado.Rows.Count > 0)
                return DataTransformer.DataTableToGenericList<T>(tblResultado);
            else
                return null;
        }
        public T GetObject<T>(string pSqlQuery, CommandType pCommandType) {
            try {
                DataTable tblResultado = GetDataTable(pSqlQuery, CommandType.StoredProcedure);
                if (tblResultado.Rows.Count > 0)
                    return DataTransformer.DataTableToObject<T>(tblResultado);
                else
                    return default(T);
            }
            catch(Exception ex) {
                throw ex;
            }
        }
    }
}
